from odoo import http
from odoo.http import request
from odoo.addons.web.controllers.main import content_disposition
import io
import xlsxwriter


class ExportShippingInstructions(http.Controller):
    @http.route('/export_shipping_instructions/<int:sb_pd_id>', auth='user', type='http', csrf=False)
    def export_shipping_instructions(self, sb_pd_id, **kw):
        source = kw.get('source', 'pd')  # 預設 pd
        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {'in_memory': True})
        worksheet = workbook.add_worksheet()
        # 標題格式
        header_format = workbook.add_format({
            'bold': True,
            'text_wrap': True,
            'valign': 'middle',
            'align': 'center',
            'bg_color': '#D9E1F2',  # 淡藍色
            'border': 1,
        })

        # 總和行格式
        total_format = workbook.add_format({
            'bold': True,
            'bg_color': '#FFFF00',  # 黃色
            'border': 1,
        })

        if source == 'pd':
            model = request.env['sb.packing.delivery'].browse(sb_pd_id)
            if not model.exists():
                return request.not_found()

            records = model.sl_line_ids

            headers = [
                'MO', 'PO', 'PN', 'PN Rev.', 'End PO', 'End PN', 'End PN Rev.',
                'MPN', 'Incoterm', 'Customer Code', 'Customer', 'Contact Address',
                'Quantity', 'Delivered', 'Packed Qty', 'TTL N.W.(kg)', 'TTL G.W.(kg)',
                'Packages', 'Currency', 'NRE', 'Unit Price', 'Total Amount',
            ]
            numeric_fields = [12, 13, 14, 15, 16, 17, 19, 21]

        elif source == 'account_check':
            model = request.env['sb.account.checking'].browse(sb_pd_id)
            if not model.exists():
                return request.not_found()

            records = model.ship_instructions_lines

            headers = [
                'MO', 'PO', 'PN', 'PN Rev.', 'End PO', 'End PN', 'End PN Rev.',
                'MPN', 'Incoterm', 'Customer Code', 'Customer', 'Contact Address',
                'Quantity', 'Delivered', 'Packed Qty', 'TTL N.W.(kg)', 'TTL G.W.(kg)',
                'Packages', 'Currency', 'NRE', 'Unit Price', 'Total Amount',
            ]
            numeric_fields = [12, 13, 14, 15, 16, 17, 19, 21]

        else:
            return request.not_found(description="Invalid source")

        # 寫標題
        worksheet.write_row(0, 0, headers, header_format)

        # 設置凍結第一行
        worksheet.freeze_panes(1, 0)

        row = 1
        col_widths = [len(h) for h in headers]

        for record in records:
            if source == 'pd':
                incoterm_name = f"[{record.incoterm.code}]{record.incoterm.name}" if record.incoterm else ''
                data = [
                    record.sale_order_id.name or '',
                    record.po_name or '',
                    record.pn or '',
                    record.pn_rev or '',
                    record.end_po or '',
                    record.end_pn or '',
                    record.end_pn_rev or '',
                    record.product_id.name or '',
                    incoterm_name,
                    record.partner_code or '',
                    record.partner.name or '',
                    record.contact_address_inline or '',
                    record.product_uom_qty or 0,
                    record.delivered_qty or 0,
                    record.packed_qty or 0,
                    record.tt_n_weight or 0,
                    record.tt_g_weight or 0,
                    record.tt_pacakge or 0,
                    record.currency_field.name or '',
                    record.nre or 0,
                    record.unit_price or 0,
                    record.ttl_amount or 0,
                ]
            else:
                incoterm_name = f"[{record.incoterm.code}]{record.incoterm.name}" if record.incoterm else ''
                data = [
                    record.sale_order_id.name or '',
                    record.po_name or '',
                    record.pn or '',
                    record.pn_rev or '',
                    record.end_po or '',
                    record.end_pn or '',
                    record.end_pn_rev or '',
                    record.product_id.name or '',
                    incoterm_name,
                    record.partner_code or '',
                    record.partner.name or '',
                    record.contact_address_inline or '',
                    record.product_uom_qty or 0,
                    record.delivered_qty or 0,
                    record.packed_qty or 0,
                    record.tt_n_weight or 0,
                    record.tt_g_weight or 0,
                    record.tt_pacakge or 0,
                    record.currency_field.name or '',
                    record.nre or 0,
                    record.unit_price or 0,
                    record.ttl_amount or 0,
                ]

            worksheet.write_row(row, 0, data)

            # 自動調整列寬
            for idx, cell in enumerate(data):
                cell_text = str(cell)
                if len(cell_text) > col_widths[idx]:
                    col_widths[idx] = len(cell_text)

            row += 1

        # 寫總和行
        total_row_idx = row  # 總和這一行

        worksheet.write(total_row_idx, 0, 'TOTAL', total_format)

        for idx in numeric_fields:
            col_letter = xlsxwriter.utility.xl_col_to_name(idx)
            formula = f'=SUM({col_letter}2:{col_letter}{row})'
            worksheet.write_formula(total_row_idx, idx, formula, total_format)

        # 自動欄寬
        for i, width in enumerate(col_widths):
            worksheet.set_column(i, i, width + 2)

        workbook.close()
        output.seek(0)

        filename = f'{model.name}.xlsx'
        response = request.make_response(
            output.read(),
            [
                ('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'),
                ('Content-Disposition', content_disposition(filename))
            ]
        )
        return response
